package com.github.carter659.spring04;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class ThemeRepositoy {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> findAll(int size, int pageNum, String tag) {

        StringBuilder builder = new StringBuilder(  );
        builder.append("select count(nlp_topic.id) num from nlp_topic_theme left join nlp_topic on " +
                "nlp_topic_theme.TOPIC = nlp_topic.TOPIC where nlp_topic_theme.TAG = '"+tag+"' order by nlp_topic.MED_TIME desc");

        SqlRowSet countResult = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
        int count = 0 ;
        while (countResult.next()) {
            count = countResult.getInt("num");
        }

        builder = new StringBuilder(  );
        builder.append("select nlp_topic.* from nlp_topic_theme left join nlp_topic on " +
                "nlp_topic_theme.TOPIC = nlp_topic.TOPIC where nlp_topic_theme.TAG ='"+tag.trim()+"' order by nlp_topic.MED_TIME desc limit ");
        builder.append(size*pageNum + "," + size);
        System.out.println(builder.toString());
        SqlRowSet rows = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
        Map<String, Object> map ;
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        while (rows.next()) {
            map = new HashMap<String,Object >();
            map.put( "id",rows.getLong( "ID" ) );
            map.put( "topic",rows.getString( "TOPIC" ) );
            map.put( "hot_score",rows.getString( "HOT_SCORE" ) );
            builder = new StringBuilder(  );
            builder.append("SELECT TIT,ID,PICTURE FROM nlp_article \n" +
                    "WHERE ID IN \n" +
                    "(SELECT ID FROM \n" +
                    "(SELECT ID,SUBSTRING_INDEX(SUBSTRING_INDEX(TOPICS, ' ', 1),'_',1) TOPIC,\n" +
                    "SUBSTRING_INDEX(SUBSTRING_INDEX(TOPICS, ' ', 1),'_',-1) SCORE \n" +
                    "FROM nlp_article \n" +
                    "WHERE TOPICS LIKE \"%"+rows.getString( "TOPIC" )+"%\") AS A \n" +
                    "WHERE TOPIC = \""+rows.getString( "TOPIC" )+"\" \n" +
                    "ORDER BY SCORE DESC ) \n" +
                    "LIMIT 4; ");
            SqlRowSet news = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
            List<Map<String,String>> lists = new ArrayList<Map<String,String>>();
            Map<String,String> article ;
            while (news.next()) {
                article = new HashMap<String,String>();
                article.put( "pic",news.getString( "PICTURE" ) );
                article.put( "tit",news.getString( "TIT" ) );
                article.put( "id",news.getString( "ID" ) );
                lists.add( article );
            }
            if(lists.size()>0){
                map.put( "pic",lists.get( 0 ).get( "pic" ) );
            }
            map.put( "news",lists );

            map.put( "num",count );

            list.add( map );
        }
        return list;

    }

    public List<String> findThemesByTopic( String topic) {

        StringBuilder builder = new StringBuilder(  );
        builder.append("select tag from nlp_topic_theme where topic = '"+topic +"' limit 0,5");

        SqlRowSet countResult = jdbcTemplate.queryForRowSet(builder.toString().toLowerCase(), new Object[] {});
        List<String> themeList = new ArrayList<String>();
        while (countResult.next()) {
            String theme = countResult.getString( "tag" );
            themeList.add( theme );
        }
        return themeList;
    }
}
